package com.mago.tis.utility;

import java.io.File;
import java.io.FileInputStream;
import java.util.ArrayList;
import java.util.Date;
import java.util.Iterator;
import java.util.List;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

/**
 * Utility class that reads the Excel from the given path
 * 
 * @author Piyush
 * 
 */
public class ExcelReader {
	public static List<List<String>> readDataFromExcel(String excelPath)
			throws ApplicationException {
		XSSFWorkbook workbook = null;
		XSSFSheet sheet = null;
		File file = null;
		Iterator<Row> rowIterator = null;
		List<List<String>> excelDataList = new ArrayList<List<String>>();
		try {
			file = new File(excelPath);

			// Check if file exists at the location
			if (file.exists()) {
				FileInputStream fileinputStream = new FileInputStream(file);

				// Create Workbook instance holding reference to .xlsx file
				workbook = new XSSFWorkbook(fileinputStream);
				sheet = workbook.getSheetAt(0);

				// Get first/desired sheet from the workbook
				if (sheet != null) {
					rowIterator = sheet.iterator();
				}

				List<String> rowDataList = null;

				// Skip the first row as it is header of Excel
				rowIterator.next();

				// Iterate through each rows one by one
				while (rowIterator.hasNext()) {
					Row row = rowIterator.next();

					// For each row, iterate through all the columns
					Iterator<Cell> cellIterator = row.cellIterator();

					// List containing excel row data
					rowDataList = new ArrayList<String>();
					int count = 0;
					while (cellIterator.hasNext()) {
						count++;
						Cell cell = cellIterator.next();
						String cellValue = "";
						
						// For time field, get the date value and format it
						if ((count == 7) || (count == 12) || (count == 27)
								|| (count == 32)) {
							Date date = cell.getDateCellValue();
							cellValue = DateUtil.getTime(date);
						} else {
							cell.setCellType(Cell.CELL_TYPE_STRING);
							cellValue = cell.getStringCellValue();
						}

						// Check the cell type and format accordingly
						// switch (cell.getCellType()) {
						// case Cell.CELL_TYPE_NUMERIC:
						// rowDataList.add("" + cell.getNumericCellValue());
						// break;
						// case Cell.CELL_TYPE_STRING:
						rowDataList.add(cellValue);
						// break;
						// }
					}
					excelDataList.add(rowDataList);
				}
				fileinputStream.close();
			}
		} catch (Exception e) {
			throw new ApplicationException(
					ApplicationConstant.EXCEL_NOT_READABLE_EXCEPTION);
		}

		return excelDataList;
	}
}
